#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time    : 2020/9/12 11:37
# @Author  : leon yan
# @Site    :
# @File    : qsqledit.py
# @Software: PyCharm

# 导入模块
import datetime
import json
import Globalist as gl
import pypyodbc


# 定义Conn
def mdb_conn(db_name, password=""):
    """
    功能：创建数据库连接
    :param db_name: 数据库名称
    :param db_name: 数据库密码，默认为空
    :return: 返回数据库连接
    """
    Str = 'Driver={Microsoft Access Driver (*.mdb)};PWD' + password + ";DBQ=" + db_name
    sConnect = pypyodbc.win_connect_mdb(Str)
    return sConnect


# 增加记录
def mdb_add(Conn, Cur, sql):
    """
    功能：向数据库插入数据
    :param Conn: 数据库连接
    :param Cur: 游标
    :param sql: sql语句
    :return: sql语句是否执行成功
    """
    try:
        Cur.execute(sql)
        Conn.commit()
        return True
    except:
        return False


# 删除记录
def mdb_del(Conn, Cur, sql):
    """
    功能：向数据库删除数据
    :param Conn: 数据库连接
    :param Cur: 游标
    :param sql: sql语句
    :return: sql语句是否执行成功
    """
    try:
        Cur.execute(sql)
        Conn.commit()
        return True
    except:
        return False


# 修改记录
def mdb_modi(Conn, Cur, sql):
    """
    功能：向数据库修改数据
    :param Conn: 数据库连接
    :param Cur: 游标
    :param sql: sql语句
    :return: sql语句是否执行成功
    """
    try:
        Cur.execute(sql)
        Conn.commit()
        return True
    except:
        return False


# 查询记录
def mdb_sel(Cur, sql):
    """
    功能：向数据库查询数据
    :param Cur: 游标
    :param sql: sql语句
    :return: 查询结果集
    """
    try:
        Cur.execute(sql)
        return Cur.fetchall()
    except:
        return []


'''查询某一条记录'''


def ReadDB(tableName, DbId=None):
    Conn = mdb_conn(gl.global_var["dbName"])
    Cur = Conn.cursor()
    if DbId:
        sql = "SELECT * FROM " + tableName + " where Id = " + str(DbId)
        sel_data = mdb_sel(Cur, sql)
        aa = []
        for i in range(len(sel_data)):
            aa.append(list(sel_data[i]))
    else:
        sql = "SELECT * FROM " + tableName + " where 1 = 1 "
        sel_data = mdb_sel(Cur, sql)
        aa = []
        for i in range(len(sel_data)):
            aa.append(list(sel_data[i]))
    Cur.close()  # 关闭游标
    Conn.close()  # 关闭数据库连接
    return aa


def ReadDB2(tableName, DbOrder=None):
    Conn = mdb_conn(gl.global_var["dbName"])
    Cur = Conn.cursor()
    if DbOrder:
        sql = "SELECT * FROM " + tableName + " order by " + str(DbOrder)
        sel_data = mdb_sel(Cur, sql)
        aa = []
        for i in range(len(sel_data)):
            aa.append(list(sel_data[i]))
    else:
        sql = "SELECT * FROM " + tableName + " where 1 = 1 "
        sel_data = mdb_sel(Cur, sql)
        aa = []
        for i in range(len(sel_data)):
            aa.append(list(sel_data[i]))
    Cur.close()  # 关闭游标
    Conn.close()  # 关闭数据库连接
    return aa


'''删除某一条记录'''


def DeleteDbRecord(tableName, dbId, fieldName=None):
    ConnSql = mdb_conn(gl.global_var["dbName"])
    Cur = ConnSql.cursor()
    if fieldName:
        Sql = "Delete FROM " + tableName + " where " + fieldName + " = " + str(dbId)
    else:
        Sql = "Delete FROM " + tableName + " where id = " + str(dbId)

    # print(Sql)
    mdb_del(ConnSql, Cur, Sql)
    Cur.close()  # 关闭游标
    ConnSql.close()  # 关闭数据库连接


'''删除多条记录'''


def DeleteDbRecord2(tableName, lstWhere, fieldName=None):
    # print(tableName, lstWhere)
    ConnSql = mdb_conn(gl.global_var["dbName"])
    Cur = ConnSql.cursor()
    if lstWhere:
        for i in range(len(lstWhere)):
            sentence1 = " Where "
            # sentence2 = " and " .join(["%s = %r" % (k, dictWhere[k]) for k in dictWhere])
            if fieldName:
                sentence2 = fieldName + " = " + str(lstWhere[i])
            else:
                sentence2 = "id= " + str(lstWhere[i])
            Sql = "Delete FROM " + tableName + " " + sentence1 + sentence2
            mdb_del(ConnSql, Cur, Sql)
    Cur.close()  # 关闭游标
    ConnSql.close()  # 关闭数据库连接


'''删除全部'''


def DeleteDb(tableName):
    ConnSql = mdb_conn(gl.global_var["dbName"])
    Cur = ConnSql.cursor()
    Sql = "Delete FROM " + tableName
    # print(Sql)
    mdb_del(ConnSql, Cur, Sql)
    Cur.close()  # 关闭游标
    ConnSql.close()  # 关闭数据库连接


'''删除数据库(同上)'''


def _delDb(tableName, theId):
    ConnSql = mdb_conn(gl.global_var["dbName"])
    Cur = ConnSql.cursor()
    Sql = "DELETE " + tableName + ".Id FROM " + tableName + " WHERE (" + tableName + ".Id = " + str(theId) + " )"
    sel_Data = mdb_del(ConnSql, Cur, Sql)
    Cur.close()  # 关闭游标
    ConnSql.close()  # 关闭数据库连接
    return sel_Data


'''保存参数数据库'''


def SaveParameterDb(tableName, lst):
    ConnSql = mdb_conn(gl.global_var["dbName"])
    Cur = ConnSql.cursor()
    bSavedOk = True
    for k in range(len(lst)):
        if tableName == "DeviceParameter":
            strUpdate = "ParameterNum= '" + str(lst[k][1]) + "',"
            strUpdate = strUpdate + "MachineType= '" + str(lst[k][2]) + "',"
            strUpdate = strUpdate + "DefaultValue= " + str(lst[k][3]) + ","
            strUpdate = strUpdate + "PlcValue= " + str(lst[k][4]) + ","
            strUpdate = strUpdate + "Descriptions= '" + str(lst[k][5]) + "'"
            '''插入'''
            strInsertValues = "'" + str(lst[k][1]) + "','" + str(lst[k][2]) + "','" + str(lst[k][3]) + "','" + \
                              str(lst[k][4]) + "','" + str(lst[k][5]) + "'"
            strField = "(ParameterNum,ParameterNum,MachineType,DefaultValue,PlcValue,Descriptions)"
        else:
            return
        if lst[k][0] == '-1':
            # print(strInsertValues)
            Sql = "Insert Into " + tableName + strField + " Values (" + strInsertValues + ")"
            # print(Sql)
            if mdb_add(ConnSql, Cur, Sql):
                pass
            else:
                bSavedOk = False
        else:
            Sql = "Update " + tableName + " Set " + strUpdate + " where Id = " + str(lst[k][0]) + ""
            # print(Sql)
            if mdb_modi(ConnSql, Cur, Sql):
                pass
            else:
                bSavedOk = False

    Cur.close()  # 关闭游标
    ConnSql.close()  # 关闭数据库连接
    if bSavedOk:
        return True
    else:
        return False


'''新建及保存用户表'''


def NewEditDb(tableName, lst):
    ConnSql = mdb_conn(gl.global_var["dbName"])
    Cur = ConnSql.cursor()
    bSavedOk = True
    if tableName == "UserTable":
        if str(lst[5]) == "administrator":
            bAdmin = 1
        else:
            bAdmin = 0
        strUpdate = "UserName= '" + str(lst[1]) + "',"
        strUpdate = strUpdate + "LoginId= '" + str(lst[2]) + "',"
        strUpdate = strUpdate + "Password= '" + str(lst[3]) + "',"
        strUpdate = strUpdate + "Phone= '" + str(lst[4]) + "',"
        strUpdate = strUpdate + "LevelType= '" + str(lst[5]) + "',"
        strUpdate = strUpdate + "Admin= '" + str(bAdmin) + "',"
        strUpdate = strUpdate + "Descriptions= '" + str(lst[6]) + "'"
        '''插入'''
        strInsertValues = "'" + str(lst[1]) + "','" + str(lst[2]) + "','" + str(lst[3]) + "','" + str(
            lst[4]) + "','" + str(lst[5]) + "','" + str(bAdmin) + "','" + str(lst[6]) + "'"
        strField = "(UserName,LoginId,Password,Phone,LevelType,Admin,Descriptions)"
    elif tableName == "TechnologyTbl":
        strUpdate = "Barcode= '" + str(lst[1]) + "',"
        strUpdate = strUpdate + "TechnologyName= '" + str(lst[2]) + "',"
        strUpdate = strUpdate + "Material= '" + str(lst[3]) + "',"
        strUpdate = strUpdate + "Height= " + str(lst[4]) + ","
        strUpdate = strUpdate + "Length= " + str(lst[5]) + ","
        strUpdate = strUpdate + "Width= " + str(lst[6]) + ","
        strUpdate = strUpdate + "Descriptions= '" + str(lst[7]) + "',"
        # strUpdate = strUpdate + "CreateDate= '" + str(lst[8]) + "',"
        strUpdate = strUpdate + "Operator= '" + str(lst[9]) + "',"
        strUpdate = strUpdate + "Status= '" + str(lst[10]) + "'"

        '''插入'''
        strInsertValues = "'" + str(lst[1]) + "','" + str(lst[2]) + "','" + str(lst[3]) + "', " \
                          + str(lst[4]) + ", " + str(lst[5]) + "," + str(lst[6]) + ",'" + str(lst[7]) \
                          + "','" + str(lst[8]) + "','" + str(lst[9]) + "','" + str(lst[10]) + "'"
        strField = "(Barcode,TechnologyName,Material,Height,Length,Width,Descriptions, CreateDate, Operator, Status)"
    else:
        return False
    if lst[0] == -1:
        Sql = "Insert Into " + tableName + strField + " Values (" + strInsertValues + ")"
        if mdb_add(ConnSql, Cur, Sql):
            pass
        else:
            bSavedOk = False
    else:
        Sql = "Update " + tableName + " Set " + strUpdate + " where Id = " + str(lst[0]) + ""
        print(Sql)
        if mdb_modi(ConnSql, Cur, Sql):
            pass
        else:
            bSavedOk = False

    Cur.close()  # 关闭游标
    ConnSql.close()  # 关闭数据库连接

    if bSavedOk:
        return True
    else:
        return False


'''保存生产记录列表'''


def NewProductLogDB(lst):
    ConnSql = mdb_conn(gl.global_var["dbName"])
    Cur = ConnSql.cursor()
    bSavedOk = True
    '''插入'''
    for i in range(len(lst)):
        strInsertValues = "'" + str(lst[i][0]) + "','" \
                          + str(lst[i][1]) + "','" \
                          + str(lst[i][2]) + "','" \
                          + str(lst[i][3]) + "','" \
                          + str(lst[i][4]) + "','" \
                          + str(lst[i][5]) + "','" \
                          + str(lst[i][6]) + "','" \
                          + str(lst[i][7]) + "'"
        strField = "(LogTime,TodayInitPower, TotalPower, TodayInitAmount,TotalAmount, DatePower, DateAmount, Operator)"
        Sql = "Insert Into ProductLog " + strField + " Values (" + strInsertValues + ")"
        try:
            mdb_add(ConnSql, Cur, Sql)
        except BaseException as e:
            print(e)
            bSavedOk = False
    Cur.close()  # 关闭游标
    ConnSql.close()  # 关闭数据库连接

    if bSavedOk:
        return True
    else:
        return False


'''查询今日记录'''


def ReadTodayDB(TblName, strwhere):
    ConnSql = mdb_conn(gl.global_var["dbName"])
    Cur = ConnSql.cursor()
    Sql = "SELECT * FROM " + TblName + " where " + strwhere
    # print(Sql)
    if Sql:
        sel_Data = mdb_sel(Cur, Sql)
        aa = []
        for i in range(len(sel_Data)):
            aa.append(list(sel_Data[i]))
    Cur.close()  # 关闭游标
    ConnSql.close()  # 关闭数据库连接
    return aa


'''保存报警记录'''


def NewAlarmLogDB(lst):
    ConnSql = mdb_conn(gl.global_var["dbName"])
    Cur = ConnSql.cursor()
    bSavedOk = True
    '''插入'''
    for i in range(len(lst)):
        strInsertValues = "'" + str(lst[i][0]) + "','" \
                          + str(lst[i][1]) + "','" \
                          + str(lst[i][2]) + "','" \
                          + str(lst[i][3]) + "','" \
                          + str(lst[i][4]) + "'"
        strField = "(AlarmTime, Descriptions, Position, Code, Operator)"
        Sql = "Insert Into AlarmLog " + strField + " Values (" + strInsertValues + ")"
        try:
            mdb_add(ConnSql, Cur, Sql)
        except BaseException as e:
            print(e)
            bSavedOk = False
    Cur.close()  # 关闭游标
    ConnSql.close()  # 关闭数据库连接

    if bSavedOk:
        return True
    else:
        return False


'''更新'''


def UpdateDB(dbName, dctUpLst, idList):
    ConnSql = mdb_conn(gl.global_var["dbName"])
    Cur = ConnSql.cursor()
    bSavedOk = True
    '''更新'''
    aa = " , ".join(["%s = %r" % (k, dctUpLst[k]) for k in dctUpLst])
    strUpdate = "Set " + aa
    for i in range(len(idList)):
        Sql = "Update " + dbName + " " + strUpdate + " where Id = " + str(idList[i]) + ""
        # print(Sql)
        try:
            mdb_modi(ConnSql, Cur, Sql)
        except BaseException as e:
            print(e)
            bSavedOk = False

    Cur.close()  # 关闭游标
    ConnSql.close()  # 关闭数据库连接

    if bSavedOk:
        return True
    else:
        return False


def UpdateDB2(dbName, dctUpLst, dctWhere):
    ConnSql = mdb_conn(gl.global_var["dbName"])
    Cur = ConnSql.cursor()
    bSavedOk = True
    '''更新'''
    aa = " , ".join(["%s = %r" % (k, dctUpLst[k]) for k in dctUpLst])
    strUpdate = "Set " + aa
    strWhere = " , ".join([" where %s = %r" % (k, dctWhere[k]) for k in dctWhere])
    Sql = "Update " + dbName + " " + strUpdate + strWhere
    print(Sql)
    try:
        mdb_modi(ConnSql, Cur, Sql)
    except BaseException as e:
        print(e)
        bSavedOk = False

    Cur.close()  # 关闭游标
    ConnSql.close()  # 关闭数据库连接

    if bSavedOk:
        return True
    else:
        return False


class DateEncoder(json.JSONEncoder):
    def default(self, obj):
        if isinstance(obj, datetime.datetime):
            return obj.strftime("%Y-%m-%d %H:%M:%S")
        else:
            return json.JSONEncoder.default(self, obj)


'''查询'''


class QSQL(object):
    def __init__(self, tblName, dictCondition, sqlOrder, sqlFieldLst, queryType=None, pageSize=None, strWhere=None):
        # gl._init()
        self.tblName = tblName
        if strWhere:
            self.strWhere = strWhere
        else:
            self.strWhere = None
        self.dictCondition = dictCondition
        self.sqlOrder = sqlOrder
        self.sqlFieldLst = sqlFieldLst
        self.sqlNum = len(sqlFieldLst)
        self.PageSize = pageSize
        if queryType is None:
            self.sql = self._createSql()
        else:
            self.sql = self._createSql2()

    def _createSql(self):
        # 自动构造select语句
        if self.PageSize:
            sentence1 = 'SELECT TOP %s ' % self.PageSize
        else:
            sentence1 = 'SELECT '
        # sentence1 = 'SELECT '
        sentence2 = ', '.join(['%s ' % (self.sqlFieldLst[i]) for i in range(len(self.sqlFieldLst))])
        sentence3 = ' from %s ' % self.tblName
        sentence4 = ' where '
        if self.strWhere:
            sentence5 = self.strWhere
        else:
            sentence5 = ' AND '.join(
                ["%s like %r" % (k, "%" + str(self.dictCondition[k]) + "%") for k in self.dictCondition])
        sentence6 = ' %s ' % self.sqlOrder
        if sentence5:
            aa = sentence1 + sentence2 + sentence3 + sentence4 + sentence5 + sentence6
        else:
            aa = sentence1 + sentence2 + sentence3 + sentence6

        # if self.PageSize:
        #     aa += "Limit %s " % self.PageSize
        return aa

    def _createSql2(self):
        # 自动构造select语句
        if self.PageSize:
            sentence1 = 'SELECT TOP %s ' % self.PageSize
        else:
            sentence1 = 'SELECT '
        # sentence1 = 'SELECT '
        sentence2 = ', '.join(['%s ' % (self.sqlFieldLst[i]) for i in range(len(self.sqlFieldLst))])
        sentence3 = ' from %s ' % self.tblName
        sentence4 = ' where '
        if self.strWhere:
            sentence5 = self.strWhere
        else:
            sentence5 = ' AND '.join(
                ["%s = %r" % (k, self.dictCondition[k]) for k in self.dictCondition])
        sentence6 = ' %s ' % self.sqlOrder
        if sentence5:
            aa = sentence1 + sentence2 + sentence3 + sentence4 + sentence5 + sentence6
        else:
            aa = sentence1 + sentence2 + sentence3 + sentence6

        # if self.PageSize:
        #     aa += "Limit %s " % self.PageSize
        return aa

    def showLst(self):
        ConnSql = mdb_conn(gl.global_var["dbName"])
        if ConnSql:
            try:
                curs = ConnSql.cursor()  # 得到一个游标对象
                curs.execute(self.sql)  # 执行一个SQL语句
                # rows = curs.fetchall()  # 得到全部的查询结果集
                if self.PageSize:
                    rows = curs.fetchmany(int(self.PageSize))
                else:
                    rows = curs.fetchall()  # 得到全部的查询结果集
                aa = []
                # print(rows)
                # print(self.sqlNum)
                for i in range(len(rows)):
                    bb = {}
                    cc = []
                    for j in range(self.sqlNum):
                        bb[self.sqlFieldLst[j]] = rows[i][j]  # 获取字典
                        cc.append(rows[i][j])  # 获取内容LIST
                    aa.append(cc)
                ConnSql.close()
                # print(aa)
                jd = json.dumps(aa, cls=DateEncoder)
                # print(jd)
                return jd
            except BaseException as e:
                print(e)
                return None
        else:
            return None

    def recorderCounter(self):
        ConnSql = mdb_conn(gl.global_var["dbName"])
        if ConnSql:
            Curs = ConnSql.cursor()  # 得到一个游标对象
            Sql = r"select count(*) from " + self.tblName
            Curs.execute(Sql)
            aa = Curs.fetchone()[0]
            ConnSql.close()
            return aa
        else:
            return None


if __name__ == '__main__':
    pass
